// ActionScript file
package vivek.project.pocketmoney.loans
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.Event;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	import mx.core.Application;
	import mx.core.UIComponent;
	
	import vivek.project.pocketmoney.admin.model.AdminConstants;
	import vivek.project.pocketmoney.admin.model.AdminDataCache;
	import vivek.project.pocketmoney.admin.model.AdminDataModel;
	import vivek.project.pocketmoney.common.CommonData;
	import vivek.project.pocketmoney.common.events.CommonEvents;
	import vivek.project.pocketmoney.common.utils.CommonConstants;
	import vivek.project.pocketmoney.common.utils.CommonUtils;
	import vivek.project.pocketmoney.expenses.events.ExpenseEvent;
	import vivek.project.pocketmoney.expenses.model.ExpenseDataModel;

	public class LoanModel extends UIComponent
	{
		private static var instance:LoanModel;
		private var edm:ExpenseDataModel=ExpenseDataModel.getInstance();
		private var _sqlConnection:SQLConnection=CommonData.getConnection();

		public static function getInstance():LoanModel
		{
			if (!instance)
			{
				instance=new LoanModel();
			}
			return instance;
		}

		public function LoanModel():void
		{
			AdminDataModel.getInstance().addEventListener("USERCHANGED", getAllLoans);
			AdminDataModel.getInstance().addEventListener("ACCOUNTADDED", getAllLoans);
			addEventListener(LoanEvents.LoanTransactionAdded, getAllLoansTransactions);
			addEventListener(LoanEvents.LoanTransactionUpdated, getAllLoansTransactions);
			addEventListener(LoanEvents.LoanAccountDeleted, getAllLoans);
			addEventListener(LoanEvents.LoanAccountAdded, getAllLoans);

			ExpenseDataModel.getInstance().addEventListener(CommonEvents.LOAN_EXPENSE_TRANSACTION_DELETED, deleteDependentLoanTransaction);
			getAllLoans(null);
			getAllLoansTransactions(null);
		}

		public function addLoanAccount(obj:Object):void
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=LoanSQLScript.ADD_NEW_ACCOUNT;
				_statement.parameters[":lm_loandesc"]=obj.loandesc;
				_statement.parameters[":lm_bank"]=obj.bank;
				_statement.parameters[":lm_principal"]=obj.principal;
				_statement.parameters[":lm_rate"]=obj.rate;
				_statement.parameters[":lm_period"]=obj.period;
				_statement.parameters[":lm_emi"]=CommonUtils.getEMI(obj.principal, obj.rate, obj.period);
				_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				dispatchEvent(new Event(LoanEvents.LoanAccountAdded));
				
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
		}

		private function getAllLoans(event:Event):void
		{
			var _statement:SQLStatement=new SQLStatement();
			//AdminDataCache.accountData = new ArrayCollection(_statement.getResult().data);
			_statement.sqlConnection=_sqlConnection;
			var arr:Array=new Array();
			_statement.text=LoanSQLScript.LOAN_GET_DATA_ALL;
			_statement.execute();
			arr=_statement.getResult().data;
			LoanDataCache.loanAccounts=new ArrayCollection(arr);
			for each (var account:Object in arr)
			{
//				LoanDataCache.loanAccounts.addItem(account);
				LoanDataCache.loanDetails.insert(account.lm_loanid, account);
			}
		}

		private function getAllLoansTransactions(event:Event):void
		{
			var _statement:SQLStatement=new SQLStatement();
			//AdminDataCache.accountData = new ArrayCollection(_statement.getResult().data);
			_statement.sqlConnection=_sqlConnection;
			var arr:Array=new Array();
			_statement.text=LoanSQLScript.LOAN_GET_ALL_TRANSACTIONS;
			_statement.execute();
			LoanDataCache.loanTransactions.source=_statement.getResult().data;
		}

		public static function calculateInterestPart(rate:Number, principal:Number, emi:Number):Number
		{
			var monthly:Number=rate / 1200;
			var newInterest:Number=monthly * principal;
			return newInterest;
		}

		public function addLoanTransaction(obj:Object, refresh:Boolean):int
		{
			var connection:SQLConnection=CommonData.getConnection();
			try
			{
				connection.begin();
				addMFundTransaction(obj, connection, refresh);
				connection.commit();
				connection.close();
				dispatchEvent(new Event(LoanEvents.LoanTransactionAdded));
			}
			catch (sqlError:SQLError)
			{
				trace("MF : loanTransactionAddition : " + sqlError.details);
				connection.rollback();
				connection.close();
				return 9;
			}
			return 0;
		}

		public function deleteLoanAccount(acc:Object):void
		{

			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				sqlConnection.begin();
				var DELETE_LOAN_ACCOUNT:String="delete from  loanmanager where  lm_loanid=:loanId";
				_statement.text=DELETE_LOAN_ACCOUNT;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":loanId"]=acc.lm_loanid;
				_statement.execute();
				sqlConnection.commit();
					//loadData();
					//edm.refreshExpenseDataCache(null);
					//PMLogger.log("EXPENSE", "EXPENSE UPDATED :" + expenseId + " , field " + fieldName + "," + value);

			}
			catch (e:Error)
			{
				sqlConnection.rollback();
				sqlConnection.close();
				trace(e.message);
			}
			finally
			{
				if (sqlConnection.connected)
					sqlConnection.close();
				_statement=null;
			}
			for each (var obj:Object in LoanDataCache.loanTransactions)
			{
				if (obj.lt_loanid == acc.lm_loanid)
					deleteLoanTransaction(obj.lt_expenseid)
			}
			dispatchEvent(new Event(LoanEvents.LoanAccountDeleted));


		}

		public function deleteLoanTransaction(txnId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				sqlConnection.begin();
				var UPDATE_LOAN_TRANSACTION_FIELD:String="delete from  loantransaction where  lt_expenseid=:loanId";
				_statement.text=UPDATE_LOAN_TRANSACTION_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":loanId"]=txnId;
				_statement.execute();
				ExpenseDataModel.getInstance().deleteDependentExpense(txnId, true, sqlConnection);
				sqlConnection.commit();
				//loadData();
				//edm.refreshExpenseDataCache(null);
				//PMLogger.log("EXPENSE", "EXPENSE UPDATED :" + expenseId + " , field " + fieldName + "," + value);
				dispatchEvent(new Event(LoanEvents.LoanTransactionUpdated));
			}
			catch (e:Error)
			{
				sqlConnection.rollback();
				sqlConnection.close();
				trace(e.message);
			}
			finally
			{
				if (sqlConnection.connected)
					sqlConnection.close();
				_statement=null;
			}
		}

		public function deleteDependentLoanTransaction(event:ExpenseEvent):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_LOAN_TRANSACTION_FIELD:String="delete from  loantransaction where  lt_expenseid=:loanId";
				_statement.text=UPDATE_LOAN_TRANSACTION_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":loanId"]=event.data.em_expenseid;
				_statement.execute();
				dispatchEvent(new Event(LoanEvents.LoanTransactionUpdated));
			}
			catch (e:Error)
			{
				sqlConnection.close();
				trace(e.message);
			}
			finally
			{
				if (sqlConnection.connected)
					sqlConnection.close();
				_statement=null;
			}
		}

		public function updateLoanField(fieldName:String, value:Object, txnId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_LOAN_TRANSACTION_FIELD:String="UPDATE loantransaction SET " + fieldName + " = :VALUE WHERE lt_expenseid=:loanId";
				_statement.text=UPDATE_LOAN_TRANSACTION_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":loanId"]=txnId;
				_statement.execute();
				var efieldName:String=fieldName;
				switch (fieldName)
				{
					case "lt_account":
						efieldName="em_account";
						break;
					case "lt_amount":
						efieldName="em_amount";
						break;
					case "lt_date":
						efieldName="em_transactiondate";
						break;
				}
				if (fieldName != "lt_rate")
				{
					ExpenseDataModel.getInstance().updateExpenseField(efieldName, value, txnId);
				}
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				dispatchEvent(new Event(LoanEvents.LoanTransactionUpdated));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}

		private function addMFundTransaction(obj:Object, connection:SQLConnection, refresh:Boolean):void
		{
			trace(new Date().toLocaleTimeString() + "MF: start add");
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=connection;
			_statement.text=LoanSQLScript.ADD_NEW_LOANTRANSACTION;
			_statement.parameters[":lt_loanid"]=obj.loanid;
			_statement.parameters[":lt_date"]=obj.date;
			_statement.parameters[":lt_amount"]=obj.amount;
			_statement.parameters[":lt_txntype"]=obj.type;
			_statement.parameters[":lt_account"]=obj.account;
			_statement.parameters[":lt_instru"]=obj.instru;
			_statement.parameters[":lt_deposittype"]=obj.deposittype;
			_statement.parameters[":lt_rate"]=obj.rate;
			_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
			var randomId:Number=new Number();
			randomId=Math.round(Math.random() * 1000000000);
			_statement.parameters[":lt_expenseid"]=randomId;
			_statement.execute();
			trace(new Date().toLocaleTimeString() + "Loan:start add- step 1 done ");
			var expRecord:Object=new Object();
			if (obj.txntype == AdminConstants.DEPOSIT)
			{
				//expRecord.account=obj.loanid;
				expRecord.account=obj.account;
				expRecord.type=AdminConstants.DEPOSIT;
					//expRecord.transferAccount=obj.account;
			}
			else
			{
				expRecord.type=AdminConstants.WITHDRAWAL;
				expRecord.account=obj.account;
				expRecord.transferAccount=obj.loanid;
			}
			expRecord.amount=obj.amount;
			expRecord.category=CommonConstants.LOAN_CATEGORY_ID;
			expRecord.payee=0;
			expRecord.memo=obj.instru;
			expRecord.date=obj.date;
			expRecord.exRate=1.0;
			expRecord.userid=AdminDataModel.getInstance().getAccountForId(obj.account).am_userid;
			if (expRecord.mt_account != 0)
				edm.addDependentExpenseRecord(expRecord, randomId, connection);
		/* if (refresh)
		   {
		   //MutualFundDataCache.mfTransactions = new ArrayCollection( _statement.getResult().data);
		   refreshMutualFundTransactions(connection);
		 } */
		}
	}
}